package cn.com.dao;

import cn.com.entity.Clazz;
import cn.com.entity.Student;
import cn.com.entity.Teacher;
import cn.com.tool.Jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * <p>Project:Academic - PerDao
 *
 * @author 黄俊玮 On Administrator
 * @version 1.0
 * @DataTime 2024/6/15 01:44:34
 * @since 1.8
 */
public class PerDao {
    public List<Student> doStudent() {
        List<Student> list = new ArrayList<>();

        String sql = "SELECT `t_user`.id AS sid,`t_user`.name AS sname,`phone`,`pwd`,clazz,`t_class`.name AS cname FROM `t_user`RIGHT JOIN `t_class`ON`t_user`.clazz = `t_class`.id WHERE `t_user`.clazz IS NOT NULL";

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = Jdbc.getConnection();
            preparedStatement = connection.prepareStatement(sql);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                int sid = resultSet.getInt("sid");
                String sname = resultSet.getString("sname");
                int user = resultSet.getInt("phone");
                String pwd = resultSet.getString("pwd");
                String clazz = resultSet.getString("clazz");
                String cname = resultSet.getString("cname");
                list.add(new Student(sid,sname,user,pwd,clazz,cname));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        Jdbc.close(resultSet,preparedStatement,connection);
        return list;
    }
    public List<Clazz> goStu(int cid, String sname, int user, String pwd, String clazz, String cname) {
        List<Clazz> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = Jdbc.getConnection();
            preparedStatement = connection.prepareStatement("select * from t_class");
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                String id = resultSet.getString("id");
                String name = resultSet.getString("name");
                list.add(new Clazz(id,name));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            Jdbc.close(resultSet,preparedStatement,connection);
        }

        return list;
    }
    public boolean updateStu(int sid, String sname, int phone, String pwd, String clazz, Student stu) {
        Connection connection = Jdbc.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet =null;
        int i = 0;
        try {

            if (stu.getUser() != phone){
                System.out.println("1");
                preparedStatement = connection.prepareStatement("select * from t_user where `phone` = "+phone+" and id != "+stu.getSid());
                resultSet = preparedStatement.executeQuery();
                if (resultSet.next()) {
                    Jdbc.close(resultSet,preparedStatement,connection);
                    return false;
                }
            }

            if (stu.getSid() != sid){
                System.out.println(2);
                preparedStatement = connection.prepareStatement("select * from t_user where `id` = "+sid+" and id !="+stu.getSid());
                resultSet = preparedStatement.executeQuery();
                if (resultSet.next()) {
                    Jdbc.close(resultSet,preparedStatement,connection);
                    return false;
                }
            }

            System.out.println(3);
            preparedStatement = connection.prepareStatement("update t_user set id = ?,name =?,phone =?,pwd =?,clazz=? where id ="+stu.getSid());
            preparedStatement.setInt(1,sid);
            preparedStatement.setString(2,sname);
            preparedStatement.setInt(3,phone);
            preparedStatement.setString(4,pwd);
            preparedStatement.setString(5,clazz);
            i = preparedStatement.executeUpdate();
            preparedStatement = connection.prepareStatement("update `t_score` set sid=? where sid =" + stu.getSid());
            preparedStatement.setInt(1,sid);
            i = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            Jdbc.close(null,preparedStatement,connection);
        }
        if (i > 0)return true;
        return false;
    }
    public void delete(int sid, int phone) {
        Connection connection =null;
        PreparedStatement preparedStatement = null;

        try {
            connection = Jdbc.getConnection();
            preparedStatement = connection.prepareStatement("delete from t_user where id =? and phone = ?;");
            preparedStatement.setInt(1,sid);
            preparedStatement.setInt(2,phone);
            preparedStatement.executeUpdate();
            preparedStatement = connection.prepareStatement("delete from `t_score` where sid =?;");
            preparedStatement.setInt(1,sid);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            Jdbc.close(null,preparedStatement,connection);
        }
    }
    public List<Clazz> addStu(){
        List<Clazz> list = new ArrayList<>();
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = Jdbc.getConnection();
            preparedStatement = connection.prepareStatement("select * from t_class");
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                String id = resultSet.getString("id");
                String name = resultSet.getString("name");
                list.add(new Clazz(id,name));
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            Jdbc.close(resultSet,preparedStatement,connection);
        }
        return list;
    }
    public boolean addDoingStu(String name, long phone, String pwd, String clazz) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = Jdbc.getConnection();
            preparedStatement = connection.prepareStatement("select * from t_user where `phone`="+phone);
            resultSet = preparedStatement.executeQuery();

            if (resultSet.next()){
                Jdbc.close(resultSet,preparedStatement,connection);
                return false;
            }
            preparedStatement = connection.prepareStatement("insert into t_user(name,`phone`,pwd,role,clazz) values (?,?,?,?,?)");
            preparedStatement.setString(1,name);
            preparedStatement.setInt(2, (int) phone);
            preparedStatement.setString(3,pwd);
            preparedStatement.setInt(4,1);
            preparedStatement.setString(5,clazz);
            int i = preparedStatement.executeUpdate();
            if (i>0){
                Jdbc.close(resultSet,preparedStatement,connection);
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            Jdbc.close(resultSet,preparedStatement,connection);
        }
        return false;
    }
    public List<Teacher> doTeacher() {
        List<Teacher> list = new ArrayList<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            connection = Jdbc.getConnection();
            preparedStatement = connection.prepareStatement("select * from t_user where role = 2");
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()){
                int id = Integer.parseInt(resultSet.getString("id"));
                String name = resultSet.getString("name");
                int phone = resultSet.getInt("phone");
                String pwd = resultSet.getString("pwd");

                list.add(new Teacher(id,name,phone,pwd));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            Jdbc.close(resultSet,preparedStatement,connection);
        }

        return list;

    }
    public boolean updateTea(int id, String name, int phone, String pwd, Teacher t) {
        Connection connection =Jdbc.getConnection();
        PreparedStatement preparedStatement = null;
        int i=0;
        try {
            preparedStatement = connection.prepareStatement("select * from t_user where `phone` ="+phone +" and id !="+t.getId());
            if (preparedStatement.executeQuery().next()) {
                Jdbc.close(null,preparedStatement,connection);
                return false;
            }
            preparedStatement = connection.prepareStatement("select * from t_user where `id` ="+id +" and id !="+t.getId());
            if (preparedStatement.executeQuery().next()) {
                Jdbc.close(null,preparedStatement,connection);
                return false;
            }
            preparedStatement = connection.prepareStatement("update t_user set id=?,name=?,phone=?,pwd=? where id ="+t.getId());
            preparedStatement.setInt(1,id);
            preparedStatement.setString(2,name);
            preparedStatement.setInt(3,phone);
            preparedStatement.setString(4,pwd);
            i = preparedStatement.executeUpdate();
            if (id != t.getId()){
                preparedStatement = connection.prepareStatement("update `t_task` set tid = '"+id+"' where tid ="+t.getId());
                preparedStatement.executeUpdate();
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            Jdbc.close(null,preparedStatement,connection);
        }
        if (i > 0){
            return true;
        }else {
            return false;
        }

    }

    public boolean addTeacher(String name, int phone, String pwd) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        int i = 0;
        try {
            connection = Jdbc.getConnection();
            preparedStatement = connection.prepareStatement("select * from t_user where `phone` = " + phone);
            resultSet = preparedStatement.executeQuery();
            if (resultSet.next()) {
                Jdbc.close(resultSet,preparedStatement,connection);
                return false;
            }
            preparedStatement = connection.prepareStatement("insert into t_user(name,`phone`,pwd,role) values ('"+name+"',"+phone+",'"+pwd+"',2)");
            i = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            Jdbc.close(resultSet,preparedStatement,connection);
        }
        if (i>0){
            return true;
        }
        return false;
    }
}
